package model.handlers;

import database.Database;
import java.sql.SQLException;
import model.*;

/**
 *
 * @author Sonny Sandberg
 */
public abstract class GraveYardHandler 
{
    public static GraveYard getExistingGraveYard(GraveYard graveYard) throws SQLException, ClassNotFoundException
    {
        Database db = new Database();
        
        graveYard.setAddress(model.handlers.AddressHandler.getExistingAddress(graveYard.getAddress()));
        if (graveYard.getAddress().getId() > 0)
        {
            String[][] results = db.getFromDB("bs_grave_yard", "WHERE name = '"+graveYard.getName()+"'", "", "", "id", "name", "fk_address_id");
            db.closeDB();

            if (results.length > 0)
            {
                graveYard.setId(Integer.parseInt(results[0][0]));
                graveYard.setName(results[0][1]);
                graveYard.setAddress(AddressHandler.getExistingAddressByID(Integer.parseInt(results[0][2])));            
            }
        }
        
        return graveYard;
    }
    
    public static GraveYard[] getGraveYardArrayByString(String searchFor) throws SQLException, ClassNotFoundException
    {
        GraveYard[] toReturn = null;
        Database db = new Database();
        
        String[][] results = db.getFromDB("bs_grave_yard", "WHERE name LIKE '%"+searchFor+"%'", "", "", "id", "name", "fk_address_id");
        db.closeDB();
        
        toReturn = new GraveYard[results.length];
        for (int i = 0; i < results.length; i++)
        {
            toReturn[i] = new GraveYard(Integer.parseInt(results[i][0]), results[i][1], AddressHandler.getExistingAddressByID(Integer.parseInt(results[i][2])));
        }
        
        return toReturn;
    }
    
    public static boolean doesGraveYardExistOnAddress(GraveYard graveYard) throws SQLException, ClassNotFoundException
    {
        boolean answer = false;
        
        Database db = new Database();
        
        String[][] results = db.getFromDB("bs_grave_yard", "WHERE name = '"+graveYard.getName()+"' AND fk_address_id = '"+graveYard.getAddress().getId()+"'", "", "", "id");
        db.closeDB();
        
        if (results.length > 0)
        {
            answer = true;
        }
        
        return answer;
    }
    
    /**
     * CREATE OR REPLACE VIEW getGraveYardAndAddress AS
        (
                SELECT
                        gy.id AS graveID,
                        gy.name AS graveName,
                        a.id AS addressID,
                        a.street AS street,
                        a.zip_code AS zipCode,
                        zc.city as city
                FROM
                        bs_grave_yard AS gy
                                LEFT JOIN bs_address AS a ON a.id = gy.fk_address_id
                                INNER JOIN bs_zip_code AS zc ON zc.zip_code = a.zip_code
        );
     * @param id
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException 
     */
    public static GraveYard getGraveYardByID(int id) throws SQLException, ClassNotFoundException
    {
        Database db = new Database();
        
        String[][] results = db.getFromDB("getGraveYardAndAddress", "WHERE graveID = "+id, "", "", "graveID", "graveName", "addressID", "street", "zipCode", "city");
        db.closeDB();
        
        if (results.length > 0)
        {
            Address address = null;
            if (results[0][2] != null)
            {
                address = new Address(Integer.parseInt(results[0][2]), results[0][3], new ZipCode(results[0][4], results[0][5]));
            }

            return new GraveYard(Integer.parseInt(results[0][0]), results[0][1], address);
        }
        else
        {
            return null;
        }
    }
}